<?php

namespace app\models;

use app\librarys\DbCacheUtility;
use app\librarys\Inteface;
use app\librarys\Tools;

/**
 * Class Order
 * @package app\models
 */
class Order
{
    /**
     * 获取系统中所有的用户订单数量
     * @return false|null|string
     */
    public static function getAllCount($institutionCode, $start_time, $end_time, $order_status, $field, $field_value,$department)
    {
        $sql = 'SELECT COUNT(1) FROM v_order WHERE 1 = 1 ';
        if ($institutionCode != '') {
            $sql = $sql . ' AND institution_code=:institutionCode';
        }

        if ($start_time != '') {
            $sql = $sql . ' AND create_datetime >= :start_time ';
        }
        if ($end_time != '') {
            $sql = $sql . ' AND create_datetime <= :end_time ';
        }
        if ($order_status != '') {
            $sql = $sql . ' AND status = :status ';
        }

        switch ($field) {
            case 'code': //订单号码
                $sql = $sql . ' AND code = :code ';
                break;
            case 'user_name': //会员姓名
                $sql = $sql . ' AND user_name = :user_name ';
                break;
            case 'receiver_name': //收货姓名
                $sql = $sql . ' AND receiver_name = :receiver_name ';
                break;
            case 'receiver_phone': //电话号码
                $sql = $sql . ' AND receiver_phone = :receiver_phone ';
                break;
            case 'goods_name': //商品名称
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_goods WHERE order_id = v_order.id AND goods_name LIKE :goods_name) ';
                break;
            case 'goods_code': //商品代码
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_goods WHERE order_id = v_order.id AND goods_code = :goods_code) ';
                break;
        }
        if ($department != '') {
            $code_tree=Department::getDepartment($department)['code_tree'];
            $sql = $sql . ' AND EXISTS(SELECT  user_id FROM (SELECT distinct user_id from t_department t  JOIN t_department_user tu on t.id=tu.department_id where code_tree like :code_tree)as department WHERE v_order.user_id=department.user_id)';
        }
        $cmd = \Yii::$app->db->createCommand($sql);
        if ($institutionCode != '') {
            $cmd->bindValue(':institutionCode', $institutionCode);
        }

        if ($start_time != '') {
            $cmd->bindValue(':start_time', $start_time);
        }
        if ($end_time != '') {
            $cmd->bindValue(':end_time', $end_time);
        }
        if ($order_status != '') {
            $cmd->bindValue(':status', $order_status);
        }
        switch ($field) {
            case 'code': //订单号码
                $cmd->bindValue(':code', $field_value);
                break;
            case 'user_name': //会员姓名
                $cmd->bindValue(':user_name', $field_value);
                break;
            case 'receiver_name': //收货姓名
                $cmd->bindValue(':receiver_name', $field_value);
                break;
            case 'receiver_phone': //电话号码
                $cmd->bindValue(':receiver_phone', $field_value);
                break;
            case 'goods_name': //商品名称
                $cmd->bindValue(':goods_name', '%' . $field_value . '%');
                break;
            case 'goods_code': //商品代码
                $cmd->bindValue(':goods_code', $field_value);
                break;
        }
        if ($department != '') {
            $cmd->bindValue(':code_tree', $code_tree. '%');
        }
        $count = $cmd->queryScalar();
        return $count;
    }

    /**
     * 获取系统中所有用户订单列表
     * @param $offset
     * @param $limit
     * @return array
     */
    public static function getAllList($offset, $limit, $institutionCode, $start_time, $end_time, $order_status, $field, $field_value,$department)
    {
        $sql = 'SELECT * FROM v_order WHERE 1 = 1 ';
        if ($institutionCode != '') {
            $sql = $sql . ' AND institution_code=:institutionCode';
        }

        if ($start_time != '') {
            $sql = $sql . ' AND create_datetime >= :start_time ';
        }
        if ($end_time != '') {
            $sql = $sql . ' AND create_datetime <= :end_time ';
        }
        if ($order_status != '') {
            $sql = $sql . ' AND status = :status ';
        }

        switch ($field) {
            case 'code': //订单号码
                $sql = $sql . ' AND code = :code ';
                break;
            case 'user_name': //会员姓名
                $sql = $sql . ' AND user_name = :user_name ';
                break;
            case 'receiver_name': //收货姓名
                $sql = $sql . ' AND receiver_name = :receiver_name ';
                break;
            case 'receiver_phone': //电话号码
                $sql = $sql . ' AND receiver_phone = :receiver_phone ';
                break;
            case 'goods_name': //商品名称
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_goods WHERE order_id = v_order.id AND goods_name LIKE :goods_name) ';
                break;
            case 'goods_code': //商品代码
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_goods WHERE order_id = v_order.id AND goods_code = :goods_code) ';
                break;
        }
        if ($department != '') {
            $code_tree=Department::getDepartment($department)['code_tree'];
            $sql = $sql . ' AND EXISTS(SELECT  user_id FROM (SELECT distinct user_id from t_department t  JOIN t_department_user tu on t.id=tu.department_id where code_tree like :code_tree)as department WHERE v_order.user_id=department.user_id)';
        }

        $sql = $sql . ' ORDER BY create_datetime DESC LIMIT :offset, :limit';

        $cmd = \Yii::$app->db->createCommand($sql);

        if ($institutionCode != '') {
            $cmd->bindValue(':institutionCode', $institutionCode);
        }

        if ($start_time != '') {
            $cmd->bindValue(':start_time', $start_time);
        }
        if ($end_time != '') {
            $cmd->bindValue(':end_time', $end_time);
        }
        if ($order_status != '') {
            $cmd->bindValue(':status', $order_status);
        }
        switch ($field) {
            case 'code': //订单号码
                $cmd->bindValue(':code', $field_value);
                break;
            case 'user_name': //会员姓名
                $cmd->bindValue(':user_name', $field_value);
                break;
            case 'receiver_name': //收货姓名
                $cmd->bindValue(':receiver_name', $field_value);
                break;
            case 'receiver_phone': //电话号码
                $cmd->bindValue(':receiver_phone', $field_value);
                break;
            case 'goods_name': //商品名称
                $cmd->bindValue(':goods_name', '%' . $field_value . '%');
                break;
            case 'goods_code': //商品代码
                $cmd->bindValue(':goods_code', $field_value);
                break;
        }
        if ($department != '') {
            $cmd->bindValue(':code_tree', $code_tree. '%');
        }

        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);

        $items = $cmd->queryAll();

        return $items;
    }

    /**
     * 获取某机构的所有用户订单总数
     * @param string $institutionCode
     * @return false|null|string
     */
    public static function getCountByInstitution($institutionCode, $start_time, $end_time, $order_status, $field, $field_value,$department)
    {
        return self::getAllCount($institutionCode, $start_time, $end_time, $order_status, $field, $field_value,$department);
    }

    /**
     * 获取某机构的用户订单列表
     * @param string $institutionCode
     * @param int $offset 分页查询起始位置
     * @param int $limit 分页查询每页数量
     * @return array
     */
    public static function getListByInstitution($institutionCode, $offset, $limit, $start_time, $end_time, $order_status, $field, $field_value,$department)
    {
        return self::getAllList($offset, $limit, $institutionCode, $start_time, $end_time, $order_status, $field, $field_value,$department);
    }

    /**
     * 获取某个用户的订单列表数量
     * @param $userId
     * @return false|null|string
     */
    public static function getListCountByUser($userId)
    {
        $cmd = \Yii::$app->db->createCommand('
            SELECT COUNT(v0.id) FROM v_order v0
            WHERE v0.user_id=:user_id ');
        $cmd->bindValue(':user_id', $userId);

        $items = $cmd->queryScalar();

        return $items;
    }

    /**
     * 获取某个用户的订单列表
     * @param $userId
     * @param $offset
     * @param $limit
     * @return array
     */
    public static function getListByUser($userId, $offset, $limit)
    {
        $cmd = \Yii::$app->db->createCommand('
            SELECT v0.* FROM v_order v0
            WHERE v0.user_id=:user_id
            ORDER BY v0.create_datetime DESC LIMIT :offset, :limit');
        $cmd->bindValue(':user_id', $userId);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);

        $items = $cmd->queryAll();

        return $items;
    }

    /**
     * 获取订单详情
     * @param int $orderId
     * @return array|false
     */
    public static function getDetail($orderId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_order WHERE id=:orderId');
        $cmd->bindValue(':orderId', $orderId);
        $item = $cmd->queryOne();
        if ($item != false) {
            $cmd = \Yii::$app->db->createCommand('SELECT t1.*,t2.is_crossborder 
                                                  FROM t_order_goods t1
                                                      INNER JOIN t_goods t2 ON t2.code = t1.goods_code
                                                      INNER JOIN v_order t3 ON t3.institution_code = t2.institution_code AND t3.id = t1.order_id
                                                  WHERE t1.order_id=:orderId');
            $cmd->bindValue(':orderId', $orderId);
            $item['goods_list'] = $cmd->queryAll();
        } else {
            $item = null;
        }

        return $item;
    }

    /**
     * 根据订单ID获取订单产品详情,
     * @param int $orderId
     * @return array|false
     */
    public static function getGoodsDetail($orderId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT t0.is_crossborder,t1.*,t1.selling_price*t3.integral_rate AS selling_integral,t1.selling_price*t3.integral_rate*t1.quantity AS selling_integral_sum,t4.image_url
            FROM t_order_goods t1
            LEFT JOIN t_order t2 ON t2.id=t1.order_id
            LEFT JOIN  t_goods t0 ON t0.institution_code=t2.institution_code AND t0.code=t1.goods_code
            LEFT JOIN t_institution t3 ON t3.`code`=t2.institution_code
            LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.goods_code AND t4.type=1
            WHERE t1.order_id=:orderId ');
        $cmd->bindValue(':orderId', $orderId);
        $item = $cmd->queryAll();

        return $item;
    }

    /**
     * 获取订单详情,并保证此订单是属于此用户的,否则不予查询
     * @param int $userId
     * @param int $orderId
     * @return array|false
     */
    public static function getDetailWithUser($userId, $orderId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_order WHERE id=:orderId AND user_id=:userId');
        $cmd->bindValue(':orderId', $orderId);
        $cmd->bindValue(':userId', $userId);
        $item = $cmd->queryOne();
        if ($item != false) {
            $cmd = \Yii::$app->db->createCommand('SELECT t1.*,t1.selling_price*t3.integral_rate AS selling_integral,t1.selling_price*t3.integral_rate*t1.quantity AS selling_integral_sum,t4.image_url
            FROM t_order_goods t1
            LEFT JOIN t_order t2 ON t2.id=t1.order_id
            LEFT JOIN t_institution t3 ON t3.`code`=t2.institution_code
            LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.goods_code AND t4.type=1
            WHERE t1.order_id=:orderId ');
            $cmd->bindValue(':orderId', $orderId);
            $item['goods_list'] = $cmd->queryAll();
        } else {
            $item = null;
        }

        return $item;
    }

    /**
     * 通过订单编号获取订单详情
     * @param $orderCode
     * @return array|false
     */
    public static function getDetailByCode($orderCode)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_order WHERE `code`=:orderCode');
        $cmd->bindValue(':orderCode', $orderCode);
        $item = $cmd->queryOne();
        if ($item != false) {
            $cmd = \Yii::$app->db->createCommand('SELECT t1.*,t1.selling_price*t3.integral_rate AS selling_integral,t1.selling_price*t3.integral_rate*t1.quantity AS selling_integral_sum,t4.image_url
            FROM t_order_goods t1
            LEFT JOIN t_order t2 ON t2.id=t1.order_id
            LEFT JOIN t_institution t3 ON t3.`code`=t2.institution_code
            LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.goods_code AND t4.type=1
            WHERE t1.order_id=:orderId');
            $cmd->bindValue(':orderId', $item['id']);
            $item['goods_list'] = $cmd->queryAll();
        } else {
            $item = null;
        }

        return $item;
    }

    /**
     * 获取某机构的所有流转订单总数
     * @param $institutionCode
     * @param int $status
     * @return false|null|string
     */
    public static function getFlowCount($institutionCode, $status = 0, $start_time, $end_time, $field, $field_value)
    {
        $sql = 'SELECT COUNT(1) FROM v_order_flow WHERE institution_code=:institutionCode ';
        if ($status != 0) {
            $sql = $sql . ' AND status=:status ';
        }

        if ($start_time != '') {
            $sql = $sql . ' AND create_datetime >= :start_time ';
        }
        if ($end_time != '') {
            $sql = $sql . ' AND create_datetime <= :end_time ';
        }

        switch ($field) {
            case 'code': //订单号码
                $sql = $sql . ' AND order_code = :code ';
                break;
            case 'user_name': //会员姓名
                $sql = $sql . ' AND user_name = :user_name ';
                break;
            case 'goods_name': //商品名称
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_flow_goods WHERE order_flow_id = v_order_flow.id AND goods_name LIKE :goods_name) ';
                break;
            case 'goods_code': //商品代码
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_flow_goods WHERE order_flow_id = v_order_flow.id AND goods_code = :goods_code) ';
                break;
        }

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionCode', $institutionCode);
        if ($status != 0) {
            $cmd->bindValue(':status', $status);
        }
        if ($start_time != '') {
            $cmd->bindValue(':start_time', $start_time);
        }
        if ($end_time != '') {
            $cmd->bindValue(':end_time', $end_time);
        }
        switch ($field) {
            case 'code': //订单号码
                $cmd->bindValue(':code', $field_value);
                break;
            case 'user_name': //会员姓名
                $cmd->bindValue(':user_name', $field_value);
                break;
            case 'goods_name': //商品名称
                $cmd->bindValue(':goods_name', '%' . $field_value . '%');
                break;
            case 'goods_code': //商品代码
                $cmd->bindValue(':goods_code', $field_value);
                break;
        }

        $count = $cmd->queryScalar();
        return $count;
    }

    /**
     * 获取某机构的流转订单列表
     * @param $institutionCode
     * @param int $status
     * @param $offset
     * @param $limit
     * @return array
     */
    public static function getFlowList($institutionCode, $status = 0, $offset, $limit, $start_time, $end_time, $field, $field_value)
    {
        $sql = 'SELECT * FROM v_order_flow WHERE institution_code=:institutionCode';
        if ($status != 0) {
            $sql = $sql . ' AND  `status`=:status';
        }

        if ($start_time != '') {
            $sql = $sql . ' AND create_datetime >= :start_time ';
        }
        if ($end_time != '') {
            $sql = $sql . ' AND create_datetime <= :end_time ';
        }

        switch ($field) {
            case 'code': //订单号码
                $sql = $sql . ' AND order_code = :code ';
                break;
            case 'user_name': //会员姓名
                $sql = $sql . ' AND user_name = :user_name ';
                break;
            case 'goods_name': //商品名称
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_flow_goods WHERE order_flow_id = v_order_flow.id AND goods_name LIKE :goods_name) ';
                break;
            case 'goods_code': //商品代码
                $sql = $sql . ' AND EXISTS (SELECT 1 from t_order_flow_goods WHERE order_flow_id = v_order_flow.id AND goods_code = :goods_code) ';
                break;
        }

        $sql = $sql . ' ORDER BY create_datetime DESC LIMIT :offset, :limit';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionCode', $institutionCode);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);

        if ($start_time != '') {
            $cmd->bindValue(':start_time', $start_time);
        }
        if ($end_time != '') {
            $cmd->bindValue(':end_time', $end_time);
        }
        switch ($field) {
            case 'code': //订单号码
                $cmd->bindValue(':code', $field_value);
                break;
            case 'user_name': //会员姓名
                $cmd->bindValue(':user_name', $field_value);
                break;
            case 'goods_name': //商品名称
                $cmd->bindValue(':goods_name', '%' . $field_value . '%');
                break;
            case 'goods_code': //商品代码
                $cmd->bindValue(':goods_code', $field_value);
                break;
        }
        if ($status != 0) {
            $cmd->bindValue(':status', $status);
        }

        $items = $cmd->queryAll();

        return $items;
    }

    /**
     * 获取流转订单详情
     * @param $orderFlowId
     * @return array|false|null
     */
    public static function getFlowDetail($orderFlowId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_order_flow WHERE id=:orderFlowId');
        $cmd->bindValue(':orderFlowId', $orderFlowId);
        $item = $cmd->queryOne();
        if ($item != false) {
            $cmd = \Yii::$app->db->createCommand('SELECT t1.*,t3.is_crossborder FROM t_order_flow_goods t1
                                                      INNER JOIN v_order_flow t2 ON t2.id = t1.order_flow_id
                                                      INNER JOIN t_goods t3 ON t3.code = t1.goods_code AND t3.institution_code = t2.institution_code
                                                    WHERE t1.order_flow_id=:orderFlowId');
            $cmd->bindValue(':orderFlowId', $orderFlowId);
            $item['goods_list'] = $cmd->queryAll();
        } else {
            $item = null;
        }

        return $item;
    }

    /**
     * 获取订单流转详情列表
     * @param string $orderCode
     * @return array
     */
    public static function getFlowListByOrderCode($orderCode)
    {
        $items = [];
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_order_user_flow WHERE order_code=:orderCode');
        $cmd->bindValue(':orderCode', $orderCode);
        $result = $cmd->queryOne();
        if ($result != false) {
            $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_order_flow_goods WHERE order_flow_id=:orderFlowId');
            $cmd->bindValue(':orderFlowId', $result['id']);
            $result['goods_list'] = $cmd->queryAll();

            $items = [$result];

            while ($result != false && $result['institution_parent_code'] != '0') {
                $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_order_institution_flow WHERE order_code=:orderCode AND institution_code=:institutionCode');
                $cmd->bindValue(':orderCode', $orderCode);
                $cmd->bindValue(':institutionCode', $result['institution_parent_code']);
                $result = $cmd->queryOne();
                if ($result != false) {
                    $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_order_flow_goods WHERE order_flow_id=:orderFlowId');
                    $cmd->bindValue(':orderFlowId', $result['id']);
                    $result['goods_list'] = $cmd->queryAll();

                    array_unshift($items, $result);
                }
            }
        }

        return $items;
    }

    /**
     * 根据系统中用户数据添加订单
     * @param $userId
     * @return int 生成的订单id
     */
    public static function addWithUser($userId, $remark, $idcard_name = '', $idcard_number = '')
    {
        $status = 1;
        $orderId = null;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_add_order_with_user(:in_user_id, :in_remark,:in_idcard_name,:in_idcard_number, @out_order_id)');
            $cmd->bindValue(':in_user_id', $userId);
            $cmd->bindValue(':in_remark', $remark);
            $cmd->bindValue(':in_idcard_name', $idcard_name);
            $cmd->bindValue(':in_idcard_number', $idcard_number);

            $status = $cmd->queryScalar();

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_id');
            $orderId = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }


        if ($status == 0) {
            $payStatus = self::pay($orderId);
        }

        return $orderId;
    }

    /**
     * 根据所传入数据产生订单
     * @param mixed $data
     * {
     *     "user_id":"xxxxxxxx-xxxxxxx-xx-xxxxx",
     *     "total_price":100.00,
     *     "remark":"this is order remark",
     *     "receiver":
     *     {
     *         "name":"receiver name",
     *         "phone":"13012345678",
     *         "address":"receiver address",
     *         "remark":"receiver remrk"
     *     },
     *     "goods_list":
     *     [
     *         {
     *             "code":"this is goods code",
     *             "name":"this is goods name",
     *             "price":100.00,
     *             "quantity":1
     *         }
     *     ]
     * }
     * @return false|null|string
     * @throws \Exception
     * @throws \yii\db\Exception
     */
    public static function addWithOpen($data)
    {
        $orderId = null;
        $orderCode = null;

        $transaction = \Yii::$app->db->beginTransaction();
        try {

            $cmd = \Yii::$app->db->createCommand('CALL p_add_order(:in_user_id, :in_total_price, :in_receiver_name, :in_receiver_phone, :in_receiver_province, :in_receiver_city, :in_receiver_region, :in_receiver_address, :in_receiver_remark, :in_remark,:in_idcard_name,:in_idcard_number, @out_order_code)');
            $cmd->bindValue(':in_user_id', $data['user_id']);


            $cmd->bindValue(':in_total_price', $data['total_price']);
            $cmd->bindValue(':in_receiver_name', $data['receiver']['name']);
            $cmd->bindValue(':in_receiver_phone', $data['receiver']['phone']);
            $cmd->bindValue(':in_receiver_province', $data['receiver']['province']);
            $cmd->bindValue(':in_receiver_city', $data['receiver']['city']);
            $cmd->bindValue(':in_receiver_region', $data['receiver']['region']);
            $cmd->bindValue(':in_receiver_address', $data['receiver']['address']);
            $cmd->bindValue(':in_receiver_remark', isset($data['receiver']['remark']) ? $data['receiver']['remark'] : '');
            $cmd->bindValue(':in_remark', isset($data['remark']) ? $data['remark'] : '');
            $cmd->bindValue(':in_idcard_name', $data['idcardName']);
            $cmd->bindValue(':in_idcard_number', $data['idcardNumber']);

            $orderId = $cmd->queryScalar();

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_code');
            $orderCode = $cmd->queryScalar();

            foreach ($data['goods_list'] as $goods) {
                $goods = (array)$goods;
                $goodsInfo = Goods::getGoodsInfoByCode($data['institution_code'], $goods['code']);
                if ($goodsInfo == null) {
                    throw new \Exception("could not found goods:[{$goods['code']}].");
                }
                if ($goodsInfo['is_shelved'] == 0) {
                    throw new \Exception("goods:[{$goods['code']}] is off the shelf.");
                }
                if ($goodsInfo['is_enabled'] == 0) {
                    throw new \Exception("goods:[{$goods['code']}] is disabled.");
                }

                $cmd = \Yii::$app->db->createCommand('CALL p_add_order_goods(:in_order_id, :in_goods_code, :in_goods_name, :in_price, :in_quantity)');
                $cmd->bindValue(':in_order_id', $orderId);
                $cmd->bindValue(':in_goods_code', $goods['code']);
                $cmd->bindValue(':in_goods_name', $goods['name']);
                $cmd->bindValue(':in_price', $goods['price']);
                $cmd->bindValue(':in_quantity', $goods['quantity']);

                $cmd->execute();
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $orderId = null;
            $transaction->rollBack();
            throw $e;
        }

        if ($orderId == null) {
            return null;
        }

        $payStatus = self::pay($orderId);

        if ($payStatus == 0) {
            self::addUserFlow($orderCode);
        }

        return $orderId;
    }

    /**
     * 根据所传入数据产生订单
     * @param mixed $data
     * {
     *     "institution_code":"jg000000",
     *     "remark":"this is order remark",
     *      "tag":"tag",
     *     "receiver":
     *     {
     *         "name":"receiver name",
     *         "phone":"13012345678",
     *         "province":"receiver province",
     *         "city":"receiver city",
     *          "region":"receiver region",
     *         "address":"receiver address",
     *         "remark":"receiver remrk"
     *     },
     *     "goods_list":
     *     [
     *         {
     *             "code":"this is goods code",
     *             "quantity":1
     *         }
     *     ]
     * }
     * @return false|null|string
     * @throws \Exception
     * @throws \yii\db\Exception
     */
    public static function addOrderWithOpenAnonymous($data)
    {
        $orderId = null;
        $orderCode = null;

        $totalPrice = 0;
        foreach ($data['goods_list'] as &$goods) {
            $goods = (array)$goods;
            $goodsInfo = Goods::getGoodsInfoByCode($data['institution_code'], $goods['code']);
            if ($goodsInfo == null) {
                throw new \Exception("could not found goods:[{$goods['code']}].");
            }
            $goods['price'] = $goodsInfo['selling_price'];
            $goods['name'] = $goodsInfo['name'];

            $totalPrice += $goodsInfo['selling_price'] * $goods['quantity'];
        }

        $data['total_price'] = $totalPrice;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_add_order_no_user(:in_institution_code, :in_total_price, :in_receiver_name, :in_receiver_phone, :in_receiver_province, :in_receiver_city, :in_receiver_region, :in_receiver_address, :in_receiver_remark, :in_remark,:in_idcard_name,:in_idcard_number, @out_order_code)');
            $cmd->bindValue(':in_institution_code', $data['institution_code']);
            $cmd->bindValue(':in_total_price', $data['total_price']);
            $cmd->bindValue(':in_receiver_name', $data['receiver']['name']);
            $cmd->bindValue(':in_receiver_phone', $data['receiver']['phone']);
            $cmd->bindValue(':in_receiver_province', $data['receiver']['province']);
            $cmd->bindValue(':in_receiver_city', $data['receiver']['city']);
            $cmd->bindValue(':in_receiver_region', $data['receiver']['region']);
            $cmd->bindValue(':in_receiver_address', $data['receiver']['address']);
            $cmd->bindValue(':in_receiver_remark', isset($data['receiver']['remark']) ? $data['receiver']['remark'] : '');
            $cmd->bindValue(':in_remark', isset($data['remark']) ? $data['remark'] : '');
            $cmd->bindValue(':in_idcard_name', $data['idcardName']);
            $cmd->bindValue(':in_idcard_number', $data['idcardNumber']);

            $orderId = $cmd->queryScalar();

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_code');
            $orderCode = $cmd->queryScalar();

            foreach ($data['goods_list'] as $goods) {
                $goods = (array)$goods;
                $goodsInfo = Goods::getGoodsInfoByCode($data['institution_code'], $goods['code']);
                if ($goodsInfo == null) {
                    throw new \Exception("could not found goods:[{$goods['code']}].");
                }
                if ($goodsInfo['is_shelved'] == 0) {
                    throw new \Exception("goods:[{$goods['code']}] is off the shelf.");
                }
                if ($goodsInfo['is_enabled'] == 0) {
                    throw new \Exception("goods:[{$goods['code']}] is disabled.");
                }

                $cmd = \Yii::$app->db->createCommand('CALL p_add_order_goods(:in_order_id, :in_goods_code, :in_goods_name, :in_price, :in_quantity)');
                $cmd->bindValue(':in_order_id', $orderId);
                $cmd->bindValue(':in_goods_code', $goods['code']);
                $cmd->bindValue(':in_goods_name', $goods['name']);
                $cmd->bindValue(':in_price', $goods['price']);
                $cmd->bindValue(':in_quantity', $goods['quantity']);
                $cmd->execute();
            }

            $cmd = \Yii::$app->db->createCommand('UPDATE t_order SET tag=:tag WHERE id=:orderId');
            $cmd->bindValue(':tag', $data['tag']);
            $cmd->bindValue(':orderId', $orderId);
            $cmd->execute();

            $transaction->commit();
        } catch (\Exception $e) {
            $orderId = null;
            $transaction->rollBack();
            throw $e;
        }

        if ($orderId == null) {
            return null;
        }
        self::addUserFlow($orderCode);

        return $orderCode;
    }

    /**
     * 根据所传入数据产生订单(包含现金支付)
     * @param mixed $data
     * {
     *     "institution_code":"jg000000",
     *     "remark":"this is order remark",
     *      "tag":"tag",
     *      "idcardName":"xxxx",
     *      "idcardNumber":"xxxxxxx",
     *      "cash":
     *      {
     *          "out_trade_no":"xxxx",
     *          "trade_no":"xxxx",
     *          "type":"wx|ali",
     *          "total_fee":100  // 单位:分
     *      },
     *     "receiver":
     *     {
     *         "name":"receiver name",
     *         "phone":"13012345678",
     *         "province":"receiver province",
     *         "city":"receiver city",
     *          "region":"receiver region",
     *         "address":"receiver address",
     *         "remark":"receiver remrk"
     *     },
     *     "goods_list":
     *     [
     *         {
     *             "code":"this is goods code",
     *             "quantity":1,
     *              "selling_price":100
     *         }
     *     ]
     * }
     * @return false|null|string  100:订单中的商品销售价与实际销售价不符; 200:跨境商品需要身份证信息
     * @throws \Exception
     * @throws \yii\db\Exception
     */
    public static function addOrderWithOpenAndCash($data)
    {
        $orderId = null;
        $orderCode = null;

        $totalPrice = 0;
        $hasCrossborder = false;
        foreach ($data['goods_list'] as &$goods) {
            $goodsInfo = Goods::getGoodsInfoByCode($data['institution_code'], $goods->code);
            if ($goodsInfo == null) {
                throw new \Exception("未找到订单中此商品:[{$goods->code}].", 100);
            }

            if ($goodsInfo['is_enabled'] == 0 || $goodsInfo['is_shelved'] == 0) {
                throw new \Exception("未找到订单中此商品:[{$goods->code}].", 100);
            }

            // 订单中的商品销售价为0
            if ($goodsInfo['selling_price'] <= 0) {
                throw new \Exception("订单中的商品销售价异常", 200);
            }

            // 订单中的商品销售价与实际销售价不符。
            if ($goodsInfo['selling_price'] != $goods->selling_price) {
                throw new \Exception("订单中的商品销售价与实际销售价不符", 300);
            }

            $goods->price = $goodsInfo['selling_price'];
            $goods->name = $goodsInfo['name'];

            $totalPrice += $goodsInfo['selling_price'] * $goods->quantity;

            if ($goodsInfo['is_crossborder'] == 1) {
                $hasCrossborder = true;
            }
        }

        // 跨境商品需要身份证信息
        if ($hasCrossborder == true) {
            if (empty($data['idcardName']) || empty($data['idcardNumber'])) {
                throw new \Exception("跨境商品需要身份证信息", 400);
            }
        }

        $data['total_price'] = $totalPrice;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_add_order_no_user(:in_institution_code, :in_total_price, :in_receiver_name, :in_receiver_phone, :in_receiver_province, :in_receiver_city, :in_receiver_region, :in_receiver_address, :in_receiver_remark, :in_remark,:in_idcard_name,:in_idcard_number, @out_order_code)');
            $cmd->bindValue(':in_institution_code', $data['institution_code']);
            $cmd->bindValue(':in_total_price', $data['total_price']);
            $cmd->bindValue(':in_receiver_name', $data['receiver']['name']);
            $cmd->bindValue(':in_receiver_phone', $data['receiver']['phone']);
            $cmd->bindValue(':in_receiver_province', $data['receiver']['province']);
            $cmd->bindValue(':in_receiver_city', $data['receiver']['city']);
            $cmd->bindValue(':in_receiver_region', $data['receiver']['region']);
            $cmd->bindValue(':in_receiver_address', $data['receiver']['address']);
            $cmd->bindValue(':in_receiver_remark', isset($data['receiver']['remark']) ? $data['receiver']['remark'] : '');
            $cmd->bindValue(':in_remark', isset($data['remark']) ? $data['remark'] : '');
            $cmd->bindValue(':in_idcard_name', $data['idcardName']);
            $cmd->bindValue(':in_idcard_number', $data['idcardNumber']);

            $orderId = $cmd->queryScalar();

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_code');
            $orderCode = $cmd->queryScalar();

            foreach ($data['goods_list'] as $goodsItem) {
                $goodsInfo = Goods::getGoodsInfoByCode($data['institution_code'], $goodsItem->code);
                if ($goodsInfo == null) {
                    throw new \Exception("could not found goods:[{$goodsItem->code}].");
                }
                if ($goodsInfo['is_shelved'] == 0) {
                    throw new \Exception("goods:[{$goodsItem->code}] is off the shelf.");
                }
                if ($goodsInfo['is_enabled'] == 0) {
                    throw new \Exception("goods:[{$goodsItem->code}] is disabled.");
                }

                $cmd = \Yii::$app->db->createCommand('CALL p_add_order_goods(:in_order_id, :in_goods_code, :in_goods_name, :in_price, :in_quantity)');
                $cmd->bindValue(':in_order_id', $orderId);
                $cmd->bindValue(':in_goods_code', $goodsItem->code);
                $cmd->bindValue(':in_goods_name', $goodsItem->name);
                $cmd->bindValue(':in_price', $goodsItem->price);
                $cmd->bindValue(':in_quantity', $goodsItem->quantity);
                $cmd->execute();
            }

            $cmd = \Yii::$app->db->createCommand('UPDATE t_order SET tag=:tag WHERE id=:orderId');
            $cmd->bindValue(':tag', $data['tag']);
            $cmd->bindValue(':orderId', $orderId);
            $cmd->execute();

            // 记录现金支付信息
            $cash = (array)$data['cash'];
            if ($cash != null) {
                $total_fee = $cash['total_fee'];
                $out_trade_no = $cash['out_trade_no'];
                $trade_no = $cash['trade_no'];
                $cashType = $cash['type'];
                $integral = Institution::getInstitution($data['institution_code'])['integral_rate'] * $total_fee / 100;


                //添加现金支付记录
                $cashPayRecordId = Tools::db_uuid();
                $cmd = \Yii::$app->db->createCommand('
                        insert into t_order_cash_payment_record(id, out_trade_no, order_code, trade_no, type, total_fee, integral, `status`, create_datetime)
                        values (:id, :out_trade_no, :order_code, :trade_no, :type, :total_fee, :integral, 1, now())');
                $cmd->bindValue(':id', $cashPayRecordId);
                $cmd->bindValue(':out_trade_no', $out_trade_no);
                $cmd->bindValue(':order_code', $orderCode);
                $cmd->bindValue(':trade_no', $trade_no);
                $cmd->bindValue(':type', $cashType);
                $cmd->bindValue(':total_fee', $total_fee);
                $cmd->bindValue(':integral', $integral);
                $cmd->execute();

//                //增加用户积分
//                $cmd = \Yii::$app->db->createCommand('update t_user  set integral=integral+:integral where id=:user_id');
//                $cmd->bindValue(':integral', $integral);
//                $cmd->bindValue(':user_id', $item['user_id']);
//                $cmd->execute();
//                //增加用户积分log
//                $cmd = \Yii::$app->db->createCommand('insert into t_user_integral_record  (id,user_id,integral,record_type,origin_id,create_datetime,remark)
//             VALUES (uuid(),:user_id,:integral,6,:origin_id,now(),null)');
//                $cmd->bindValue(':integral', $item['integral']);
//                $cmd->bindValue(':user_id', $item['user_id']);
//                $cmd->bindValue(':origin_id', $item['id']);
//                $cmd->execute();

                // 处理机构及其所有上级机构的帐
                $cmd = \Yii::$app->db->createCommand('select code_tree from t_institution where code=:institutionCode');
                $cmd->bindValue(':institutionCode', $data['institution_code']);
                $codeTree = $cmd->queryScalar();
                $institutionCodes = str_split($codeTree, 8);
                foreach ($institutionCodes as $institutionCode) {
                    //增加机构账户余额
                    $cmd = \Yii::$app->db->createCommand('update t_institution_account set balance=balance+:total_fee where institution_code=:institution_code');
                    $cmd->bindValue(':total_fee', $total_fee / 100);
                    $cmd->bindValue(':institution_code', $institutionCode);
                    $cmd->execute();
                    //增加机构账户余额 log
                    $cmd = \Yii::$app->db->createCommand('insert into t_institution_account_record  (id,institution_code,amount,record_type,origin_id,create_datetime,remark)
             VALUES (uuid(),:institution_code,:amount,4,:origin_id,now(),null)');
                    $cmd->bindValue(':amount', $total_fee / 100);
                    $cmd->bindValue(':institution_code', $institutionCode);
                    $cmd->bindValue(':origin_id', $cashPayRecordId);
                    $cmd->execute();
                }
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $orderId = null;
            $transaction->rollBack();
            throw $e;
        }

        if ($orderId == null) {
            return null;
        }
        try {
            self::addUserFlow($orderCode);
        } catch (\Exception $e) {

        }
        return $orderCode;
    }

    public static function addWithImport($data)
    {
        $orderId = null;
        $orderCode = null;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            if ($data['isPay'] == 0) {
                $cmd = \Yii::$app->db->createCommand('CALL p_add_order_no_user(:in_institution_code, :in_total_price, :in_receiver_name, :in_receiver_phone, :in_receiver_province, :in_receiver_city, :in_receiver_region, :in_receiver_address, :in_receiver_remark, :in_remark,:in_idcard_name,:in_idcard_number, @out_order_code)');
                $cmd->bindValue(':in_institution_code', $data['institution_code']);
            } else {
                $cmd = \Yii::$app->db->createCommand('CALL p_add_order(:in_user_id, :in_total_price, :in_receiver_name, :in_receiver_phone, :in_receiver_province, :in_receiver_city, :in_receiver_region, :in_receiver_address, :in_receiver_remark, :in_remark,:in_idcard_name,:in_idcard_number, @out_order_code)');
                $cmd->bindValue(':in_user_id', $data['user_id']);
            }

            $cmd->bindValue(':in_total_price', $data['total_price']);
            $cmd->bindValue(':in_receiver_name', $data['receiver']['name']);
            $cmd->bindValue(':in_receiver_phone', $data['receiver']['phone']);
            $cmd->bindValue(':in_receiver_province', $data['receiver']['province']);
            $cmd->bindValue(':in_receiver_city', $data['receiver']['city']);
            $cmd->bindValue(':in_receiver_region', $data['receiver']['region']);
            $cmd->bindValue(':in_receiver_address', $data['receiver']['address']);
            $cmd->bindValue(':in_receiver_remark', isset($data['receiver']['remark']) ? $data['receiver']['remark'] : '');
            $cmd->bindValue(':in_remark', isset($data['remark']) ? $data['remark'] : '');
            $cmd->bindValue(':in_idcard_name', $data['idcardName']);
            $cmd->bindValue(':in_idcard_number', $data['idcardNumber']);

            $orderId = $cmd->queryScalar();

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_code');
            $orderCode = $cmd->queryScalar();

            foreach ($data['goods_list'] as $goods) {
                $goods = (array)$goods;
                $goodsInfo = Goods::getGoodsInfoByCode($data['institution_code'], $goods['code']);
                if ($goodsInfo == null) {
                    throw new \Exception("could not found goods:[{$goods['code']}].");
                }
                if ($goodsInfo['is_shelved'] == 0) {
                    throw new \Exception("goods:[{$goods['code']}] is off the shelf.");
                }
                if ($goodsInfo['is_enabled'] == 0) {
                    throw new \Exception("goods:[{$goods['code']}] is disabled.");
                }

                $cmd = \Yii::$app->db->createCommand('CALL p_add_order_goods(:in_order_id, :in_goods_code, :in_goods_name, :in_price, :in_quantity)');
                $cmd->bindValue(':in_order_id', $orderId);
                $cmd->bindValue(':in_goods_code', $goods['code']);
                $cmd->bindValue(':in_goods_name', $goods['name']);
                $cmd->bindValue(':in_price', $goods['price']);
                $cmd->bindValue(':in_quantity', $goods['quantity']);

                $cmd->execute();
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $orderId = null;
            $transaction->rollBack();
            throw $e;
        }

        if ($orderId == null) {
            return null;
        }
        if ($data['isPay'] == 0) {
            $payStatus = self::addUserFlow($orderCode);
        } else {
            $payStatus = self::pay($orderId);
        }
        if ($payStatus) {
            throw new \Exception("用户订单生成失败！请检查用户[" . $data['receiver']['name'] . "]剩余积分是否充足！");
        }

        return $orderId;
    }

    /**
     * 订单支付
     * @param $orderId
     * @return false|null|string  0:支付成功; 1:失败(系统错误) 10:订单状态不对,不能进行支付; 20:用户积分不足,支付失败
     */
    public static function pay($orderId)
    {
        $status = 1;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_pay_order(:in_order_id)');
            $cmd->bindValue(':in_order_id', $orderId);

            $status = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }


        if ($status == '0') {
            $cmd = \Yii::$app->db->createCommand('SELECT `code` FROM t_order WHERE id=:order_id');
            $cmd->bindValue(':order_id', $orderId);

            $orderCode = $cmd->queryScalar();

            self::addUserFlow($orderCode);
        }

        return $status;
    }

    /**
     * 添加用户流转订单
     * @param $orderCode
     * @return false|null|string  0:成功; 1:失败
     */
    public static function addUserFlow($orderCode)
    {
        $status = 1;
        $orderFlowId = null;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_add_order_user_flow(:in_order_code, @out_order_flow_id)');
            $cmd->bindValue(':in_order_code', $orderCode);

            $status = $cmd->queryScalar();

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_flow_id');
            $orderFlowId = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }


        if ($status == '0') {
            self::payFlow($orderFlowId);
        }

        return $status;
    }

    /**
     * 添加机构流转订单
     * @param $orderCode
     * @return false|null|string  0:成功; 1:失败(系统错误); 10:此流转订单已到系统，无需再流转了; 20:未找到需要流转的订单
     */
    public static function addInstitutionFlow($orderCode)
    {
        $status = 1;
        $orderFlowId = null;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_add_order_institution_flow(:in_order_code, @out_order_flow_id)');
            $cmd->bindValue(':in_order_code', $orderCode);

            $status = $cmd->queryScalar();  // 0:成功; 10:此流转订单已到系统，无需再流转了; 20:未找到需要流转的订单

            $cmd = \Yii::$app->db->createCommand('SELECT @out_order_flow_id');
            $orderFlowId = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        if ($status == 0) {
            self::payFlow($orderFlowId);
        } else if ($status == 10) {  // 此流转订单已到系统,则将次订单传入业务系统
            $status = self::sync($orderCode);
        }

        return $status;
    }

    /**
     * 流转订单支付
     * @param $orderFlowId
     * @return false|null|string  0:成功; 1:失败(系统错误) 10:订单状态不对,不能进行支付; 110:订单处理为"待审核订单"; 112:订单处理为“未结算订单”
     */
    public static function payFlow($orderFlowId)
    {

        $status = 1;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_pay_order_flow(:in_order_flow_id)');
            $cmd->bindValue(':in_order_flow_id', $orderFlowId);

            $status = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        if ($status == '0') {
            $cmd = \Yii::$app->db->createCommand('SELECT order_code FROM t_order_flow WHERE id=:order_flow_id');
            $cmd->bindValue(':order_flow_id', $orderFlowId);

            $orderCode = $cmd->queryScalar();

            self::addInstitutionFlow($orderCode);
        }
        return $status;
    }

    /**
     * 审核订单
     * @param $orderFlowId
     * @return false|null|string  0:审核成功; 10:失败(订单状态不对)
     */
    public static function approveFlow($orderFlowId)
    {
        $cmd = \Yii::$app->db->createCommand('CALL p_approve_order_flow(:in_order_flow_id)');
        $cmd->bindValue(':in_order_flow_id', $orderFlowId);

        $status = $cmd->queryScalar();
        if ($status == 0) {
            self::payFlow($orderFlowId);
        }
        return $status;
    }

    /**
     * 订单同步成功(进入业务系统)
     * @param $orderCode
     * @return false|null|string
     * @throws \Exception
     */
    public static function sync($orderCode)
    {
        $orderDetail = self::getDetailByCode($orderCode);
        if ($orderDetail != null) {
            $orderData = [
                'orderCode' => $orderDetail['code'],
                'ordertype' => 'integral',
                'shrName' => $orderDetail['receiver_name'],
                'sheng' => $orderDetail['receiver_province'],
                'shi' => $orderDetail['receiver_city'],
                'qu' => $orderDetail['receiver_region'],
                'address' => $orderDetail['receiver_address'],
                'mobile' => $orderDetail['receiver_phone'],
                'deskphone' => '',
                'email' => '',
                'amount' => 0,
                'beizhu' => $orderDetail['remark'],
                'idcard_name' => $orderDetail['idcard_name'],
                'idcard_number' => $orderDetail['idcard_number'],
                'goods' => []
            ];
            foreach ($orderDetail['goods_list'] as $goods) {
                $goodsData = [
                    'goods_code' => $goods['goods_code'],
                    'goods_name' => $goods['goods_name'],
                    'goods_number' => $goods['quantity'],
                    'price' => 0
                ];

                array_push($orderData['goods'], $goodsData);
            }
            Inteface::setOrder($orderData);
//            if ($setOrderStatus->status == 1) {//20160905 删除 原因：由于接口转到了公用统一接口，统一接口返回的结构有变

            $status = 0;
            $transaction = \Yii::$app->db->beginTransaction();
            try {
                $cmd = \Yii::$app->db->createCommand('CALL p_order_sync(:in_order_code)');
                $cmd->bindValue(':in_order_code', $orderCode);

                $status = $cmd->queryScalar();

                $transaction->commit();
            } catch (\Exception $e) {
                $transaction->rollBack();
                $status = 1;
            }

//            } else {//20160905 删除 原因：由于接口转到了公用统一接口，统一接口返回的结构有变
//                throw  new \Exception($setOrderStatus->info);//20160905 删除 原因：由于接口转到了公用统一接口，统一接口返回的结构有变
//            }//20160905 删除 原因：由于接口转到了公用统一接口，统一接口返回的结构有变
        } else {
            throw new \Exception('未找到此订单');
        }

        return $status;
    }

    /**
     * 获取近7天每天的所有订单总量和销售总量
     * @return array
     */
    public static function getReportWithQuantityAndSellingPrice()
    {
        $cmd = \Yii::$app->db->createCommand('
            SELECT tt0.create_date, ifnull(tt1.quantity, 0) AS quantity, ifnull(tt1.total_selling_price, 0) AS total_selling_price FROM
              (SELECT date_add( DATE (now()), INTERVAL @i:=@i-1 DAY ) AS create_date, 0 AS quantity
               FROM ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS tmp,
                 ( SELECT @i:= 1) t) tt0
              LEFT JOIN  (
                          SELECT sum(t0.total_selling_price) AS  total_selling_price, count(1) AS quantity, DATE (t0.create_datetime) AS create_date FROM t_order_flow t0
                            LEFT JOIN t_institution t1 ON t1.code=t0.institution_code
                          WHERE t1.parent_code=\'0\'
                          GROUP BY DATE (t0.create_datetime)
                        ) tt1 on tt0.create_date=tt1.create_date
            ORDER BY tt0.create_date');

        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

    /**
     * 获取当前年内所有月该机构的销售统计
     * @param $institution_code
     * @param $isAdmin
     * @return array
     */
    public static function getReportWithQuantityAndSellingPriceByMonth($institution_code, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = $filter . ' tt1.code = :institution_code ';
        } else {
            $filter = $filter . ' tt1.parent_code = :institution_code ';
        }
        $sql = "SELECT t1.m as date,ifnull( t2.selling_price,0) as selling_price, ifnull(t2.quantity,0) as quantity FROM (
                SELECT concat(year(now()),'-',t.m) AS m FROM (
                                  SELECT '01' AS m UNION ALL
                                  SELECT '02' UNION ALL
                                  SELECT '03' UNION ALL
                                  SELECT '04' UNION ALL
                                  SELECT '05' UNION ALL
                                  SELECT '06' UNION ALL
                                  SELECT '07' UNION ALL
                                  SELECT '08' UNION ALL
                                  SELECT '09' UNION ALL
                                  SELECT '10' UNION ALL
                                  SELECT '11' UNION ALL
                                  SELECT '12' ) as t) t1
                LEFT JOIN (
                    SELECT
                      sum(tt0.total_selling_price) AS selling_price,
                      count(1)                    AS quantity,
                      DATE_FORMAT(tt0.create_datetime, '%Y-%m') as m
                    FROM t_order_flow tt0
                      LEFT JOIN t_institution tt1 ON tt1.code = tt0.institution_code
                    WHERE tt0.status = 13 AND year(tt0.create_datetime) = year(now()) 
                          AND $filter
                    GROUP BY  DATE_FORMAT(tt0.create_datetime, '%Y-%m')
                    ) t2 ON t2.m = t1.m
                ORDER BY t1.m";

        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institution_code);
        } else {
            $cmd->bindValue(':institution_code', '0');
        }

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取当前年内所有季度该机构的销售统计
     * @param $institution_code
     * @param $isAdmin
     * @return array
     */
    public static function getReportWithQuantityAndSellingPriceByQuarter($institution_code, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = $filter . ' tt1.code = :institution_code ';
        } else {
            $filter = $filter . ' tt1.parent_code = :institution_code ';
        }
        $sql = "SELECT t1.q as date,ifnull( t2.selling_price,0) as selling_price, ifnull(t2.quantity,0) as quantity FROM (
                    SELECT concat(year(now()),'年', '第一季度') AS q,1 AS qv UNION ALL
                    SELECT concat(year(now()),'年', '第二季度'),2 UNION ALL
                    SELECT concat(year(now()),'年', '第三季度'),3 UNION ALL
                    SELECT concat(year(now()),'年', '第四季度'),4) AS t1
                LEFT JOIN (
                    SELECT
                      sum(tt0.total_selling_price) AS selling_price,
                      count(1)                    AS quantity,
                      QUARTER(tt0.create_datetime) as qv
                    FROM t_order_flow tt0
                      LEFT JOIN t_institution tt1 ON tt1.code = tt0.institution_code
                    WHERE  tt0.status = 13 AND year(tt1.create_datetime) = year(now())
                           AND $filter
                    GROUP BY  QUARTER(tt0.create_datetime)
                    ) t2 ON t2.qv = t1.qv
                ORDER BY t1.qv";
        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institution_code);
        } else {
            $cmd->bindValue(':institution_code', '0');
        }

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取该机构按年的销售统计
     * @param $institution_code
     * @param $isAdmin
     * @return array
     */
    public static function getReportWithQuantityAndSellingPriceByYear($institution_code, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = $filter . ' tt1.code = :institution_code ';
        } else {
            $filter = $filter . ' tt1.parent_code = :institution_code ';
        }
        $sql = "SELECT
                  sum(tt0.total_selling_price) AS selling_price,
                  count(1)                    AS quantity,
                  year(tt0.create_datetime) as date
                FROM t_order_flow tt0
                  LEFT JOIN t_institution tt1 ON tt1.code = tt0.institution_code
                WHERE tt0.status = 13 AND $filter
                GROUP BY  year(tt0.create_datetime)";

        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institution_code);
        } else {
            $cmd->bindValue(':institution_code', '0');
        }

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 取消订单
     * 1,2,3 ,9,10,11订单状态 订单可以取消
     * @param $order_code
     * @param $remark
     * @return false|null|string  0正常 20未找到此订单 21订单不能取消
     */
    public static function cancelOrder($order_code, $remark)
    {
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_cancel_order(:in_order_code,:in_remark)');
            $cmd->bindValue(':in_order_code', $order_code);
            $cmd->bindValue(':in_remark', $remark);

            $status = $cmd->queryScalar();
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    /**
     * 设置订单配送单信息
     * @param $orderDelivery
     * @return int
     * @throws \yii\db\Exception
     */
    public static function setOrderDelivery($orderDelivery)
    {
        $status = 0;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_set_order_delivery(:in_boss_code,:in_order_code, :in_logistics_company, :in_logistics_code)');
            $cmd->bindValue(':in_boss_code', $orderDelivery['order_delivery_code']);
            $cmd->bindValue(':in_order_code', $orderDelivery['order_code']);
            $cmd->bindValue(':in_logistics_company', $orderDelivery['logistics_company']);
            $cmd->bindValue(':in_logistics_code', $orderDelivery['logistics_code']);

            $orderDeliveryId = $cmd->queryScalar();

            foreach ($orderDelivery['goods_list'] as $item) {
                $cmd = \Yii::$app->db->createCommand('CALL p_set_order_delivery_goods(:in_order_delivery_id, :in_goods_code, :in_quantity)');
                $cmd->bindValue(':in_order_delivery_id', $orderDeliveryId);
                $cmd->bindValue(':in_goods_code', $item->goods_code);
                $cmd->bindValue(':in_quantity', $item->quantity);

                $cmd->execute();
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    /**
     * 获取订单的配送单信息
     * @param $orderCode
     * @return mixed|null
     */
    public static function getOrderDelivery($orderCode)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_order_delivery WHERE order_code=:orderCode');
        $cmd->bindValue(':orderCode', $orderCode);
        $items = DbCacheUtility::queryAll($cmd);

        foreach ($items as &$item) {
            $cmd = \Yii::$app->db->createCommand('
                SELECT t0.*, t3.goods_name, t2.institution_code FROM t_order_delivery_goods t0
                LEFT JOIN t_order_delivery t1 ON t1.id=t0.order_delivery_id
                LEFT JOIN t_order t2 ON t1.order_code=t2.`code`
                LEFT JOIN t_order_goods t3 ON t3.order_id=t2.id AND t3.goods_code=t0.goods_code
                WHERE t0.order_delivery_id=:orderDeliveryId');
            $cmd->bindValue(':orderDeliveryId', $item['id']);

            $item['goods_list'] = DbCacheUtility::queryAll($cmd);
        }

        return $items;

    }

    /**
     * 根据订单配送单信息ID 获取订单的配送单信息详情
     * @param $id
     * @return mixed|null
     */
    public static function getOrderDeliveryDetail($id)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_order_delivery WHERE id=:id');
        $cmd->bindValue(':id', $id);
        $item = DbCacheUtility::queryOne($cmd);

        if ($item != null && $item != '') {
            $cmd = \Yii::$app->db->createCommand('
                SELECT t1.*,t4.image_url from t_order_delivery_goods t1
                LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.goods_code AND t4.type=1
                WHERE t1.order_delivery_id=:orderDeliveryId');
            $cmd->bindValue(':orderDeliveryId', $id);

            $item['goods_list'] = DbCacheUtility::queryAll($cmd);
        }

        return $item;

    }

    /**
     * 修改订单状态为"已完成"
     * @param $orderCode
     * @return int
     * @throws \yii\db\Exception
     */
    public static function setOrderCompleted($orderCode)
    {
        $status = 0;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_set_order_completed(:in_order_code)');
            $cmd->bindValue(':in_order_code', $orderCode);
            $cmd->execute();
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    /**
     * 通过tag来获取对应的订单列表数量
     * @param $institutionCode
     * @param $tag
     * @return false|null|string
     */
    public static function getOrderCountWithTag($institutionCode, $tag)
    {
        $cmd = \Yii::$app->db->createCommand('
                SELECT COUNT(1) FROM v_order
                WHERE institution_code=:institutionCode AND tag=:tag');
        $cmd->bindValue(':institutionCode', $institutionCode);
        $cmd->bindValue(':tag', $tag);
        $item = $cmd->queryScalar();

        return $item;
    }

    /**
     * 通过tag来获取对应的订单列表
     * @param $institutionCode
     * @param $tag
     * @param $offset
     * @param $limit
     * @return array
     */
    public static function getOrderListWithTag($institutionCode, $tag, $offset, $limit)
    {
        $cmd = \Yii::$app->db->createCommand('
                SELECT * FROM v_order
                WHERE institution_code=:institutionCode AND tag=:tag
                ORDER BY create_datetime DESC
                LIMIT :offset, :limit');
        $cmd->bindValue(':institutionCode', $institutionCode);
        $cmd->bindValue(':tag', $tag);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);

        $items = $cmd->queryAll();

        return $items;
    }


    /**
     * 获取订单总数(不要状态已取消，作废，拆分)
     * @param $institutionCode
     * @param $isAdmin
     * @return array
     */
    public static function getOrderCount($institutionCode, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = ' AND institution_code = :institution_code';
        }
        $sql = "SELECT 'day' as dateType,count(1) AS count
                FROM t_order
                WHERE status != 31 AND status != 32 AND status != 33 AND date_format(modify_datetime,'%Y-%m-%d') = curdate() $filter
                UNION ALL
                SELECT 'week' as dateType,count(1) AS count
                FROM t_order
                WHERE status != 31 AND status != 32 AND status != 33 
                      AND date_format(modify_datetime,'%Y-%m-%d') >= subdate(curdate(),date_format(curdate(),'%w')-1)
                      AND date_format(modify_datetime,'%Y-%m-%d')<subdate(curdate(),date_format(curdate(),'%w')-8) $filter
                UNION ALL
                SELECT 'month' as dateType,count(1) AS count
                FROM t_order
                WHERE status != 31 AND status != 32 AND status != 33 
                      AND date_format(modify_datetime,'%Y-%m-%d') >= DATE_ADD(curdate(),interval -day(curdate())+1 day)
                      AND date_format(modify_datetime,'%Y-%m-%d') < DATE_ADD(curdate()-day(curdate())+1,interval 1 month) $filter";
        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institutionCode);
        }
        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取订单统计数量(完成)
     * @param $institutionCode
     * @param $isAdmin
     * @return array
     */
    public static function getOrderNumberCount($institutionCode, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = ' AND institution_code = :institution_code';
        }
        $sql = "SELECT 'day' as dateType,count(1) AS count
                FROM t_order
                WHERE (status = 23 OR status = 30) AND date_format(modify_datetime,'%Y-%m-%d') = curdate() $filter
                UNION ALL
                SELECT 'week' as dateType,count(1) AS count
                FROM t_order
                WHERE (status = 23 OR status = 30)
                      AND date_format(modify_datetime,'%Y-%m-%d') >= subdate(curdate(),date_format(curdate(),'%w')-1)
                      AND date_format(modify_datetime,'%Y-%m-%d')<subdate(curdate(),date_format(curdate(),'%w')-8) $filter
                UNION ALL
                SELECT 'month' as dateType,count(1) AS count
                FROM t_order
                WHERE (status = 23 OR status = 30)
                      AND date_format(modify_datetime,'%Y-%m-%d') >= DATE_ADD(curdate(),interval -day(curdate())+1 day)
                      AND date_format(modify_datetime,'%Y-%m-%d') < DATE_ADD(curdate()-day(curdate())+1,interval 1 month) $filter";
        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institutionCode);
        }
        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取订单统计金额
     * @param $institutionCode
     * @param $isAdmin
     * @return array
     */
    public static function getOrderPriceCount($institutionCode, $isAdmin)
    {
        $filter = '';
        if (!$isAdmin) {
            $filter = ' AND institution_code = :institution_code';
        }
        $sql = "SELECT 'day' as dateType,sum(total_selling_price) AS price
                FROM t_order
                WHERE (status = 23 OR status = 30) AND date_format(modify_datetime,'%Y-%m-%d') = curdate() $filter
                UNION ALL
                SELECT 'week' as dateType,sum(total_selling_price) AS price
                FROM t_order
                WHERE (status = 23 OR status = 30)
                      AND date_format(modify_datetime,'%Y-%m-%d') >= subdate(curdate(),date_format(curdate(),'%w')-1)
                      AND date_format(modify_datetime,'%Y-%m-%d')<subdate(curdate(),date_format(curdate(),'%w')-8) $filter
                UNION ALL
                SELECT 'month' as dateType,sum(total_selling_price) AS price
                FROM t_order
                WHERE (status = 23 OR status = 30)
                      AND date_format(modify_datetime,'%Y-%m-%d') >= DATE_ADD(curdate(),interval -day(curdate())+1 day)
                      AND date_format(modify_datetime,'%Y-%m-%d') < DATE_ADD(curdate()-day(curdate())+1,interval 1 month) $filter";
        $cmd = \Yii::$app->db->createCommand($sql);
        if (!$isAdmin) {
            $cmd->bindValue(':institution_code', $institutionCode);
        }
        $result = $cmd->queryAll();
        return $result;
    }


    public static function backOrderGoods($order_code, $goods_code, $quantity, &$message)
    {
        $status = 0;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            //1-获取订单信息
            $sql = 'SELECT t_order.institution_code,t_order.user_id,t_order_goods.selling_price,t_order_goods.quantity 
                    FROM t_order 
                    INNER JOIN t_order_goods ON t_order_goods.order_id = t_order.id 
                    WHERE t_order.code = :order_code AND t_order_goods.goods_code = :goods_code';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':order_code', $order_code);
            $cmd->bindValue(':goods_code', $goods_code);
            $result = $cmd->queryOne();
            if ($result == false) {
                $transaction->rollBack();
                $message = '无效的订单编码';
                return false;
            }

            $institution_code = $result['institution_code'];
            $user_id = $result['user_id'];
            $selling_price = $result['selling_price'];
            $goods_quantity = $result['quantity'];

            //2-获取机构积分汇率
            $sql = 'SELECT integral_rate FROM t_institution where code = :code';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':code', $institution_code);
            $result = $cmd->queryOne();

            if ($result == false) {
                $transaction->rollBack();
                $message = '无效的机构积分汇率';
                return false;
            }
            $integral_rate = $result['integral_rate'];

            //3-添加退货记录
            $backId = Tools::db_uuid();
            $sql = 'INSERT INTO t_back_goods_record(id,institution_code,order_code,goods_code,quantity,create_datetime)
                    VALUES (:id,:institution_code,:order_code,:goods_code,:quantity,now())';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':id', $backId);
            $cmd->bindValue(':institution_code', $institution_code);
            $cmd->bindValue(':order_code', $order_code);
            $cmd->bindValue(':goods_code', $goods_code);
            $cmd->bindValue(':quantity', $quantity);

            $result = $cmd->execute();
            if ($result == 0) {
                $transaction->rollBack();
                $message = '添加退货记录失败';
                return false;
            }

            //4-退还用户积分
            //4.1判断数量
            if ($quantity > $goods_quantity) {
                $transaction->rollBack();
                $message = '退货商品的数量大于了订单商品的数量';
                return false;
            }
            //4.2添加用户退还积分记录 record_type 10 订单退货返回的积分
            $sql = 'INSERT INTO t_user_integral_record(id,user_id,integral,record_type,origin_id,create_datetime,remark)
                    VALUES (:id,:user_id,:integral,10,:origin_id,now(),:remark)';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':id', Tools::db_uuid());
            $cmd->bindValue(':user_id', $user_id);
            $cmd->bindValue(':integral', $selling_price * $integral_rate * $quantity);
            $cmd->bindValue(':origin_id', $backId);
            $cmd->bindValue(':remark', '用户退货返回积分');

            $result = $cmd->execute();
            if ($result == 0) {
                $transaction->rollBack();
                $message = '添加用户退还积分记录失败';
                return false;
            }

            //4.3更新用户账户积分
            $sql = 'UPDATE t_user SET integral = integral + :add_integral WHERE id = :id';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':add_integral', $selling_price * $integral_rate * $quantity);
            $cmd->bindValue(':id', $user_id);
            $result = $cmd->execute();

            if ($result == 0) {
                $transaction->rollBack();
                $message = '更新用户账户积分失败';
                return false;
            }

            //5退还机构金额
            $sql = 'SELECT t_order_flow.institution_code,t_order_flow_goods.settlement_price,t_order_flow_goods.goods_code
                    FROM t_order_flow
                      INNER JOIN t_order_flow_goods ON t_order_flow_goods.order_flow_id = t_order_flow.id
                    WHERE t_order_flow.order_code = :order_code AND t_order_flow_goods.goods_code = :goods_code';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':order_code', $order_code);
            $cmd->bindValue(':goods_code', $goods_code);
            $items = $cmd->queryAll();
            foreach ($items as $item) {
                //5.1保存机构账户积分变更记录 record_type 5 账户余额增加(订单退货)
                $sql = 'INSERT INTO t_institution_account_record(id,institution_code,amount,record_type,origin_id,create_datetime,remark)
                        VALUES (:id,:institution_code,:amount,5,:origin_id,now(),:remark)';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':id', Tools::db_uuid());
                $cmd->bindValue(':institution_code', $item['institution_code']);
                $cmd->bindValue(':amount', $item['settlement_price'] * $quantity);
                $cmd->bindValue(':origin_id', $backId);
                $cmd->bindValue(':remark', '订单退货');
                $result = $cmd->execute();
                if ($result == 0) {
                    $transaction->rollBack();
                    $message = '保存机构账户积分变更记录失败';
                    return false;
                }

                //5.2修改机构账户积分
                $sql = 'UPDATE t_institution_account SET balance = balance + :balance WHERE institution_code = :institution_code';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':balance', $item['settlement_price'] * $quantity);
                $cmd->bindValue(':institution_code', $item['institution_code']);
                $result = $cmd->execute();
                if ($result == 0) {
                    $transaction->rollBack();
                    $message = '修改机构账户积分失败';
                    return false;
                }
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status == 0;
    }


    /**
     * 根据订单code获取退货信息
     * @param $order_code
     * @return array
     */
    public static function getBackGoodsRecord($order_code)
    {
        $sql = 'SELECT t_back_goods_record.*,t_goods.name AS goods_name,t_order_goods.selling_price*t_institution.integral_rate AS selling_price
                FROM t_back_goods_record
                  INNER JOIN t_goods ON t_goods.code = t_back_goods_record.goods_code AND t_goods.institution_code = t_back_goods_record.institution_code
                  INNER JOIN t_order ON t_order.code = t_back_goods_record.order_code
                  INNER JOIN t_order_goods ON t_order_goods.order_id = t_order.id AND t_order_goods.goods_code = t_back_goods_record.goods_code
                  INNER JOIN t_institution ON t_institution.code = t_back_goods_record.institution_code
                WHERE t_back_goods_record.order_code = :order_code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':order_code', $order_code);
        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 根据订单code获取流转机构的退货记录
     * @param $order_code
     * @param string $institution_code
     * @return array
     */
    public static function getBackGoodsRecordForFlow($order_code, $institution_code = '')
    {
        $sql = '';
        if ($institution_code == '') {
            $sql = 'SELECT t_back_goods_record.*,t_goods.name AS goods_name,t_order_flow_goods.settlement_price,t_institution.name AS institution_name
                    FROM t_back_goods_record
                      INNER JOIN t_goods ON t_goods.code = t_back_goods_record.goods_code AND t_goods.institution_code = t_back_goods_record.institution_code
                      INNER JOIN t_order_flow ON t_order_flow.order_code = t_back_goods_record.order_code AND t_order_flow.institution_code = t_back_goods_record.institution_code
                      INNER JOIN t_order_flow_goods ON t_order_flow_goods.order_flow_id = t_order_flow.id AND t_order_flow_goods.goods_code = t_back_goods_record.goods_code
                      INNER JOIN t_institution ON t_institution.code = t_order_flow.institution_code
                    WHERE t_back_goods_record.order_code = :order_code';
        } else {
            $sql = 'SELECT t_back_goods_record.*,t_goods.name AS goods_name,t_order_flow_goods.settlement_price ,t_institution.name AS institution_name
                    FROM t_back_goods_record
                      INNER JOIN t_goods ON t_goods.code = t_back_goods_record.goods_code AND t_goods.institution_code = t_back_goods_record.institution_code
                      INNER JOIN t_order_flow ON t_order_flow.order_code = t_back_goods_record.order_code AND t_order_flow.institution_code = t_back_goods_record.institution_code
                      INNER JOIN t_order_flow_goods ON t_order_flow_goods.order_flow_id = t_order_flow.id AND t_order_flow_goods.goods_code = t_back_goods_record.goods_code
                      INNER JOIN t_institution ON t_institution.code = t_order_flow.institution_code
                    WHERE  t_back_goods_record.order_code = :order_code AND t_order_flow.institution_code = :institution_code';

        }

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':order_code', $order_code);
        if ($institution_code != '') {
            $cmd->bindValue(':institution_code', $institution_code);
        }
        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 根据传入的钉钉系统订单号来查询这些订单在三方中的状态
     * @param $walfareOrderCodeList
     * @return array
     */
    public static function getOrderStatusList($walfareOrderCodeList)
    {
        if (count($walfareOrderCodeList) == 0) {
            return [];
        }

        $tagSql = '';
        foreach ($walfareOrderCodeList as $item) {
            $tagSql = $tagSql . "'" . $item . "',";
        }
        $tagSql = substr($tagSql, 0, -1);

        $cmd = \Yii::$app->db->createCommand("SELECT id, `code`, `status`, tag FROM t_order WHERE tag IN($tagSql)");
        $orderList = $cmd->queryAll();

        return $orderList;
    }

    /**
     * 根据钉钉的订单编号查询此订单的配送信息
     * @param $orderCode
     * @return array
     */
    public static function getOrderDeliveryInfo($orderCode)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT t0.* FROM t_order_delivery t0 INNER JOIN t_order t1 ON t1.code = t0.order_code WHERE t1.tag=:orderCode');
        $cmd->bindValue(':orderCode', $orderCode);
        $items = $cmd->queryAll();
        return $items;
    }

    /**
     * 根据配送单id查询配送单中包含的商品列表
     * @param $deliveryId
     * @return array
     */
    public static function getDeliveryGoodsList($deliveryId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_order_delivery_goods WHERE order_delivery_id=:deliveryId');
        $cmd->bindValue(':deliveryId', $deliveryId);
        $items = $cmd->queryAll();
        return $items;
    }
}